package utils.msg;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import model.DataBase;
import model.EventFieldConditionDetail;
import model.EventFieldsCondition;
import model.Field;
import model.FieldsType;
import model.Table;
import model.UserEvent;
import model.UserEventConditonType;
import model.UserEventDetail;

import org.apache.commons.lang.StringUtils;
import org.hibernate.validator.xml.FieldType;

import utils.JdbcUtil;
import dao.EventFieldsConditionDao;
import dao.FieldDao;

public class QueryUtil {
	
	
	
	public static void main(String[] args) {
		UserEventDetail ued = new UserEventDetail();
		UserEvent userEvent = new UserEvent();
		userEvent.setId(1);
		userEvent.setuId(1);
		userEvent.setEtId(1);
		userEvent.settId(1);
		userEvent.setDesc("这是一个测试事件");
		userEvent.setPollingTime(5000);
		userEvent.setCustomTime("2016-04-10 15:37:36.000");
		userEvent.setTimeSpan(6000);
		userEvent.setType("AND");
		
		DataBase dataBase = new DataBase();
		dataBase.setDbName("Test");
		dataBase.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		dataBase.setUrl("jdbc:sqlserver://localhost:1433;DataBaseName=Test");
		dataBase.setUserName("sa");
		dataBase.setPassword("sa123456");
		
		Table table = new Table();
		table.setId(1);
		table.setDbId(1);
		table.setTableName("noise");
		
		ued.setDataBase(dataBase);
		ued.setTable(table);
		ued.setUserEvent(userEvent);
		
		//QueryResult queryResult = queryByUserEventDetail(ued);
		//System.out.println(queryResult);
		
		QueryUtil queryUtil = new QueryUtil();
		QueryResult queryResult = queryUtil.queryFieldsByUserEventDetail(ued);
		System.out.println(queryResult);
		
		String sender ="IUE";
		System.out.println(queryUtil.generateMsg(sender, ued));
		
		
		//Enum.valueOf(FieldsType, "1");
		//System.out.println(valueOf);
		//List<String> filterList = strList.stream().filter(e->!e.equals("2")).collect(Collectors.toList());
		//filterList.forEach(System.out::println);
		
	}
	


	
	
	private String sql;
	private Field fieldTimeStamp;
	private List<EventFieldConditionDetail> efcdList;
	
	/**
	 * 使用UserEventDetail进行监测，如果监测到事件，生成message。如果没有监测到，返回null
	 * @param sender
	 * @param ued
	 * @return
	 */
	public String generateMsg(String sender,UserEventDetail ued){
		QueryResult queryResult = queryFieldsByUserEventDetail(ued);
		if (queryResult==null)
			return null;
		MsgModel msgModel = new MsgModel(sender, queryResult, ued, efcdList);
		return msgModel.generateMsg();
	}
	
	/**
	 * 根据UserEventDetail进行动态多条件查询，对拼接sql等进行优化。已经查询过的放入成员属性，下次查询不需要重复查询
	 * @param ued
	 * @return
	 */
	private QueryResult queryFieldsByUserEventDetail(UserEventDetail ued) {
		QueryResult queryResult = null;
		if (sql==null || fieldTimeStamp==null || efcdList==null){
			//1.根据userEvent的tId获得type为TimeStamp的字段
			UserEvent userEvent = ued.getUserEvent();
			fieldTimeStamp = getTimeStampFieldByUserEvent(userEvent);
			String timeStampPart="";
			if (!StringUtils.isBlank(fieldTimeStamp.getFieldName())){
				timeStampPart = fieldTimeStamp.getFieldName()+" between dateadd(ms,-"+userEvent.getTimeSpan()+",getDate()) and  getDate()";
			}
			
			//2.根据userEvent的ueId获得其监控字段与location字段的列表
			efcdList = getEventFieldConditionDetailListByUserEvent(userEvent);
			
			sql = generateSql(ued, efcdList, fieldTimeStamp, timeStampPart);
		}
		queryResult = queryBySqlAndEfcList(ued.getDataBase(),sql,efcdList,fieldTimeStamp);
		return queryResult;
	}
	
	
	/**
	 * 根据UserEventDetail生成sql并进行查询，返回QueryResult
	 * @param ued
	 * @return 如果没有查询到，返回null
	 */
	public static QueryResult queryByUserEventDetail(UserEventDetail ued) {
		//1.根据userEvent的tId获得type为TimeStamp的字段
		UserEvent userEvent = ued.getUserEvent();
		Field fieldTimeStamp = getTimeStampFieldByUserEvent(userEvent);
		String timeStampPart="";
		if (!StringUtils.isBlank(fieldTimeStamp.getFieldName())){
			timeStampPart = fieldTimeStamp.getFieldName()+" between dateadd(ms,-"+userEvent.getTimeSpan()+",getDate()) and  getDate()";
		}
		
		//2.根据userEvent的ueId获得其监控字段与location字段的列表
		List<EventFieldConditionDetail> efcdList = getEventFieldConditionDetailListByUserEvent(userEvent);
		
		//3.生成sql
		String sql = generateSql(ued, efcdList, fieldTimeStamp, timeStampPart);
		System.out.println(sql);
		//4.根据sql进行查询
		QueryResult queryResult = queryBySqlAndEfcList(ued.getDataBase(),sql,efcdList,fieldTimeStamp);
		
		return queryResult ;
	}

	/**
	 * 根据UserEventDetail生成相应的查询Sql
	 * @param ued
	 * @param efcdList
	 * @param fieldTimeStamp
	 * @param timeStampPart
	 * @return
	 */
	private static String generateSql(UserEventDetail ued,
			List<EventFieldConditionDetail> efcdList, Field fieldTimeStamp,
			String timeStampPart) {
		String fieldsPart = "";
		String conditionPart = "";
		
		
		EventFieldConditionDetail locationEfcd = null;
		//取出代表location的efcd，放到后面单独处理
		for (int i = 0; i < efcdList.size(); i++) {
			String type = efcdList.get(i).getField().getType();
			if(type.equals(FieldsType.LOCATION.value())){
				locationEfcd = efcdList.remove(i);
				break;
			}
		}
		//efcdList.add(locationEfcd);//将location的efcd移到列表末尾
		//组装monitor字段的fieldsPart与conditionPart
		if(efcdList.size()>0){
			Field field = efcdList.get(0).getField();
			EventFieldsCondition efc = efcdList.get(0).getEfc();
			if (efcdList.size()==1){
				fieldsPart = field.getFieldName();
				conditionPart = fieldsPart+ efc.getCondition()+efc.getValue();
			}else{
				fieldsPart = String.join(",",getFieldNames(efcdList) );
				String type = ued.getUserEvent().getType();//取值为AND或OR 
				conditionPart = String.join(" "+type+" ", getConditions(efcdList));
			}
		}
		
		//组装locationPart
		String locationFieldName = null;
		if (locationEfcd!=null){
			locationFieldName = locationEfcd.getField().getFieldName();
			conditionPart += " and "+locationFieldName+locationEfcd.getEfc().getCondition()+locationEfcd.getEfc().getValue();
		}
		//fieldPart中总是将location放在倒数第2，timeStamp放到倒数第1的位置
		if(locationFieldName!=null && !StringUtils.isBlank(locationFieldName)){
			fieldsPart += ","+locationFieldName;
		}
		fieldsPart += ","+fieldTimeStamp.getFieldName();
		
		efcdList.add(locationEfcd);//将location的efcd移到列表末尾
		//3.根据1、2步的得到组合生成查询条件
		//String sql = "select "+fieldsPart+" from "+ued.getTable().getTableName()+" where "+ conditionPart+" and "+timeStampPart;
		String sql = "select "+fieldsPart+" from "+ued.getTable().getTableName()+" where "+ conditionPart+" and "+timeStampPart;

		return sql;
	}
	
	private static QueryResult queryBySqlAndEfcList(DataBase dataBase,String sql,List<EventFieldConditionDetail> efcdList,Field fieldTimeStamp){
		if (StringUtils.isBlank(sql))
			return null;
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        QueryResult queryResult = null;
		try {
			con = getConnectionByDataBase(dataBase);
			System.out.println("QueryUtil.queryBySqlAndEfcList(),sql="+sql);
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				queryResult = new QueryResult();
				List<String> fieldNameList = new ArrayList<String>();
				List<FieldsType> fieTypeList = new ArrayList<FieldsType>();
				for (EventFieldConditionDetail efcd : efcdList) {
					fieldNameList.add(efcd.getField().getFieldName());
					String type = efcd.getField().getType();
					fieTypeList.add(FieldsType.getInstance(type));
				}
				//最后一个字段Timestamp不在efcdList,需要手动加入
				fieTypeList.add(FieldsType.getInstance(FieldsType.TIMESTAMP.value()));
				
				fieldNameList.add(fieldTimeStamp.getFieldName());
				List<String> valueList = new ArrayList<String>();
				for (String e : fieldNameList) {
					String value = rs.getString(e);
					valueList.add(value);
				}
				queryResult.setFieldNames(fieldNameList);
				queryResult.setFieldTypes(fieTypeList);
				queryResult.setValues(valueList);
                
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return queryResult;
	}

	private static List<EventFieldConditionDetail> getEventFieldConditionDetailListByUserEvent(
			UserEvent userEvent) {
		EventFieldsConditionDao efcDao = new EventFieldsConditionDao();
		List<EventFieldConditionDetail> efcdList = efcDao.getEventFieldConditionDetailsByUeId(userEvent.getId());
		return efcdList;
	}

	private static Field getTimeStampFieldByUserEvent(UserEvent userEvent) {
		FieldDao fDao = new FieldDao();
		Field fieldTimeStamp  = fDao.getFieldNameByTidAndType(userEvent.gettId(), FieldsType.TIMESTAMP.value());
		return fieldTimeStamp;
	}
	
	
	
	private static String[] getFieldNames(List<EventFieldConditionDetail> efcdList){
		EventFieldConditionDetail[] efcds = efcdList.toArray(new EventFieldConditionDetail[0]);
		String[] fieldNames = new String[efcds.length];
		for (int i = 0; i < efcds.length; i++) {
			fieldNames[i] = efcds[i].getField().getFieldName();
		}
		return fieldNames;
	}
	
	private static String[] getConditions(List<EventFieldConditionDetail> efcdList){
		EventFieldConditionDetail[] efcds = efcdList.toArray(new EventFieldConditionDetail[0]);
		String[] conditions = new String[efcds.length];
		for (int i = 0; i < efcds.length; i++) {
			String fieldName = efcds[i].getField().getFieldName();
			conditions[i] = fieldName+efcds[i].getEfc().getCondition()+efcds[i].getEfc().getValue();
		}
		return conditions;
	}
	
	public static Connection getConnectionByDataBase(DataBase dataBase) throws SQLException{
		try {
			Class.forName(dataBase.getDriver());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
        return DriverManager.getConnection(dataBase.getUrl(),dataBase.getUserName(),dataBase.getPassword());
	}
}
